from flask import current_app as app
from sqlalchemy import text,select,insert,update,delete,literal_column
from sqlalchemy.sql import and_,or_,func
from sqlalchemy.engine import Connection,CursorResult,LegacyRow,Transaction
from eim import db
from eim.DataBasModals.mode import t_codepool,t_codegen
import time
import hashlib

class DBTool:
    """
    数据库操作帮助类
    """
    def __init__(self,db):
        """
        数据库操作工具类的构造函数
        """
        self.db = db
    
    # 获取未用编号
    def __getCodeFromPool(self,tblname,datepart,con:Connection)->dict:
        """
        从号码池中获取指定表的尚未占用的编码
        根据表名查询待使用的编码，如果存在返回第一个编码否则返回null

        Args:
            tblname (_type_): 表名称
            con (_type_): 数据库连接

        Returns:
            dict: 编码字典对象
        """
        qry = select(t_codepool)
        qry = qry.where(text(f"tablename='{tblname}'"))
        qry = qry.where(text(f"datepart='{datepart}'"))
        qry=qry.with_for_update()
        
        codelist:list=[]
        
        for row in con.execute(qry).fetchall():
            codelist.append(row._asdict())
            
        if (len(codelist)>0):
            return codelist[0]
        else:
            return {}
    
    # 获取编码规则
    def __getTableCodeRule(self,con:Connection,tblname:str,prechar:str,datePart:str="",bitlen:int=4)->dict:
        """
        获取指定数据表的编码规则，如果编码规则不存在，添加一个并返回，
        如果编码规则与指定的存在差异，按照新的规则更新并返回
        最终编码规则为[prechar][date]serialno
        Args:
            tblname (str): 数据表名称
            prechar (str): 字符前缀
            datePart (str, optional): 日期部分. Defaults to ''.
            serialLen (int, optional): 顺序号长度. Defaults to 4.

        Returns:
            dict: 返回编码规则
        """
        try:
            curdate = time.strftime('%Y%m%d')
            rulelist:list = []
            
            qry=select(t_codegen)
            qry = qry.where(text(f"tname='{tblname}'"))
            qry = qry.where(text(f"datepart='{datePart}'")).limit(1).offset(0)
            qry = qry.with_for_update()
            
            for row in con.execute(qry).fetchall():
                rulelist.append(row._asdict())
            
            if len(rulelist)>0:
                # 存在指定表的编码规则
                currule = rulelist[0]
                if((currule['tname']!=tblname) or (currule['prechar']!=prechar) or (currule['datepart']!=datePart) or (currule['bitlen']!=bitlen)):
                    # 编码规则与当前指定的不相同时将数据库中的规则更新成当前的规则
                    vals = {
                        "tname": tblname,
                        "prechar": prechar,
                        "datepart": datePart,
                        "bitlen":bitlen
                    }
                    currule['tname'] = tblname
                    currule['prechar'] = prechar
                    currule['datepart'] = datePart
                    currule['bitlen'] = bitlen
                    id=currule["ID"]
                    qry = update(t_codegen).values(vals)
                    qry = qry.where(text(f"id='{id}'"))
                    con.execute(qry)
                else:
                    # 当前规则与数据库规则相同时不需要处理
                    pass
                # 返回当前规则
                return currule
            else:
                #数据库中不存在规则时添加规则并返回新的规则
                currule:dict = {"tname": tblname,"prechar": prechar,"datepart": datePart,"bitlen":bitlen,"seriallen":0}
                qry = insert(t_codegen).values(currule)
                result:CursorResult = con.execute(qry)
                currule["ID"]=result.inserted_primary_key[0]
                currule["seriallen"]=0
                return currule            
        except Exception as es:
            raise es
    
    # 生成新的顺序码
    def __makeSerialNO(self,curvalue:int,serialLen:int,datePart:str,prechar:str)->str:
        """
        生成顺序码

        Args:
            curvalue (int): 当前的值
            serialLen (int): 顺序码长度
            datePart (str): 编码日期部分
            prechar (str): 字符前缀

        Returns:
            str: 返回顺序码
        """
        try:
            newval = curvalue+1
            zlen = serialLen-len(str(newval))
            newcode = zlen*'0'+str(newval)
            return f"{prechar}{datePart}{newcode}"
        except Exception as es:
            raise es
   
    # 获取新的编码
    def getNewCode(self,tname:str,prechar:str,datePart:str='',bitlen:int=4)->str:
        """
        获取新的编码

        Args:
            tname (str): 使用编码的数据表
            prechar (str): 字符前缀
            datePart (str, optional): 是否使用日期段. Defaults to ''.
            bitlen (int, optional): 顺序码长度. Defaults to 4.

        Returns:
            str: 新的编码
        """
        con:Connection = db.engine.connect()
        trans:Transaction = con.begin()
        
        try:
            # 从编码池中获取编码
            code_inpool = self.__getCodeFromPool(tname,datePart,con)
            if(len(code_inpool)<=0):
                # 编码池中没有可用的编码
                currule:dict = self.__getTableCodeRule(con,tname,prechar,datePart,bitlen)
                newcode = self.__makeSerialNO(currule["seriallen"],currule["bitlen"],datePart,prechar)
                # 更新编码规则表，将当前顺序号的值增加1
                uval = {"seriallen":currule["seriallen"]+1}
                ruleid = currule["ID"]
                qry = update(t_codegen).values(uval)
                qry = qry.where(text(f"ID='{ruleid}'"))
                con.execute(qry)
                trans.commit()
                return newcode
            else:
                # 编码池中存在未使用的编码时
                codeid = code_inpool["ID"]
                newcode = code_inpool["code"]
                
                # 删除用过的编码
                qry = delete(t_codepool)
                qry = qry.where(text(f"ID='{codeid}'"))
                con.execute(qry)
                trans.commit()
                return newcode
        except Exception as es:
            trans.rollback()
            raise es
    
    # 退回尚未使用的编号
    def returnCode(self,tname:str,datePart:str,rcode:str)->int:
        """
        退回尚未使用的编码到编码池中

        Args:
            tname (str): 编码的表名
            datePart (str): 编码的日期部分
            rcode (str): 待退回的编码

        Returns:
            int: 返回受影响行数
        """
        
        try:
            vals = {"tablename":tname,"datepart":datePart,"code":rcode}
            qry = insert(t_codepool).values(vals)
            con:Connection = db.engine.connect()
            result:CursorResult = con.execute(qry)
            return result.rowcount
        except Exception as es:
            raise es
    
    # 对字符串加密主要用于登录密码的加密
    def encryLoginPassword(self,pwd:str)->str:
        """
        加密字符串
        Args:
            pwd (str): 原文

        Returns:
            str: 密文
        """
        try:
            sha1 = hashlib.sha1()
            sha1.update(pwd.encode("utf8"))
            postpwd = sha1.hexdigest()
            return postpwd
        except Exception as es:
            raise es
        
    def genDBcolumns(self,dbtable,strCols:list):
        """
        从字符串列表构建数据表列

        Args:
            dbtable (type): 数据表
            strCols (list): 字符串列名列表
        """
        try:
            res:list=[]
            if len(strCols)>0:
                for item in strCols:
                    col = dbtable.c[item]
                    res.append(col)
            else:
                res.append(dbtable)
            
            return res
        except Exception as es:
            raise Exception(f"查询数据列出错：{es}")